﻿
-- =============================================
-- Author:		<Author,@XFL>
-- Create date: <Create Date,2012 02 16>
-- Description:	<Description,获取一个员工的工资 用于员工出勤扣款提供工资数据 @XFL 2012 02 16>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_Wage_GetItem_UserName] 
(
@UserName nvarchar(50)
)
AS
BEGIN
	Select 
    Ename
	,(isnull((select CompanyName from Company where id=E.CompanyId),'--')) as CompanyName 
	,(isnull((select Wage from Grade where id=E.GradeId),0)) as BasicWage  --基本工资
	,(isnull((select p.PostWage from Post p where p.id=E.PostId),0)) as PostWage  --岗位工资
	,(datediff(dd,ISNULL(e.RuZhiDate,GetDate()),GetDate())/365*100) as WorkWage  --工龄工资
	,(isnull((select JxWage from Post where id=E.PostId),0)) as JxWage  --绩效工资
	,(isnull((select ManQin from Post where id=E.PostId),0)) as ManQin  --满勤奖励
	,(isnull((SELECT top(1) isnull(QtAllowance,0) FROM Employee_Wage w where w.UserName=E.UserName  order by w.CreateDate desc),0)) as QtAllowance  --其他补助
	,IsNull((Select Sum(FA.Amount) From Employee_FixedAllowance FA Where FA.UserName = E.UserName ),0) as FAllowance  --固定补助
	,ISNULL((Select Sum(EA.Amount) From Employee_Allowance EA Where PostId=e.PostId and EA.Pid=E.Pid and EA.Cid=E.Cid),0) as Allowance  --地域补助
	,ruzhidate
	From Employee E
	Where E.UserName=@UserName

END

	RETURN